# -*- coding:utf-8 -*-
"""
简单封装xlwt
author: pylixm
date: 2017-07-03
"""
import os
import xlwt
from xlwt import XFStyle, Borders, Formatting, Font, Pattern
from django.conf import settings
from django.http import HttpResponse


class XLS(object):
    def __init__(self, sheet='Sheets', border_style=1):
        """
        初始化实例
        :param sheet: excel的sheet工作表
        :param border_style:边框样式
        """
        self.wb = xlwt.Workbook()  # 工作表文件
        self.ws = self.wb.add_sheet(sheet, cell_overwrite_ok=True)
        self.col_num = 0
        self.row_num = 0
        # 初始化样式，边框的宽度都置为border_style
        borders = Borders()
        borders.left = border_style
        borders.right = border_style
        borders.top = border_style
        borders.bottom = border_style
        # 初始化字典格式，加粗
        pattern = Formatting.Pattern()
        pattern.pattern = Pattern.SOLID_PATTERN
        pattern.pattern_fore_colour = 22
        self.style = XFStyle()
        self.style.borders = borders
        self.header_style = XFStyle()
        self.header_style.borders = borders
        self.header_style.pattern = pattern
        self.title_style = self.first_title_style()

    def add_sheet(self, sheet):
        # 增加sheet页面
        # self.ws = self.wb.add_sheet(sheet.decode('gbk'))
        self.ws = self.wb.add_sheet(sheet)
        self.col_num = 0
        self.row_num = 0

    def save(self, name):
        # 保存Excel，用户指定文件名
        self.wb.save(name)

    def set_title(self, item, col_begin, col_end):
        self.ws.write_merge(self.row_num, self.row_num, col_begin, col_end, item, self.title_style)
        self.row_num += 1

    def set_header(self, headers, col=None, row=None):
        if not col:
            col = self.col_num
        if row:
            self.row_num = row
        thiscol = col
        for header in headers:
            self.ws.write(self.row_num, thiscol, header, self.header_style)
            thiscol += 1
        self.row_num += 1

    def set_content(self, lines, col=None, row=None):
        """
        lines 格式：[[1,b,c],[d,e,f]]
        :param lines:
        :param col:
        :param row:
        :return:
        """
        if not col:
            col = self.col_num
        if row:
            self.row_num = row
        for line in lines:
            thiscol = col
            for item in line:
                self.ws.write(self.row_num, thiscol, str(item), self.style)
                thiscol += 1
            self.row_num += 1

    def first_title_style(self):
        """
        设置title样式
        :return:
        """
        font = Font()
        font.name = 'Arial'
        font.colour_index = 0x08
        font.height = 0x0150  ##字号
        font.bold = True
        ##颜色值可以对照pyExceleratoe中的examples下的formate.xls
        # 边框
        borders = Borders()
        borders.left = 1
        borders.right = 1
        borders.top = 1
        borders.bottom = 1
        # 对齐
        alignment = Formatting.Alignment()
        alignment.horz = 0x02
        alignment.vert = 0x01
        style = XFStyle()
        style.font = font  ##字号应该大一点
        style.borders = borders  ##无边线
        style.alignment = alignment  # 水平和垂直均居中
        return style


# 导出excel
def render_to_excel(header_list, contents, filename, title=None):
    """ 需要传递的参数有表头内容 , 内容 ，文件名   excel """
    myxls = XLS()
    if title:
        myxls.set_title(title, 0, len(header_list) - 1)
    myxls.set_header(header_list)  ##写表头
    myxls.set_content(contents)  ##写内容
    path = os.path.join('download/', filename)
    myxls.save(path)
    r = HttpResponse(open(path, 'rb').read())
    r['Content-Disposition'] = 'attachment; filename="%s"' % filename
    r['Content-Type'] = 'application/octet-stream'
    return r


def out_to_excel(header_list, contents, filename, title=None):
    """ 需要传递的参数有表头内容 , 内容 ，文件名   excel """
    myxls = XLS()
    if title:
        myxls.set_title(title, 0, len(header_list) - 1)
    myxls.set_header(header_list)  ##写表头
    myxls.set_content(contents)  ##写内容
    path = os.path.join('download/', filename)
    myxls.save(path)
    return filename.strip('.xls')


def out_to_excel_sheets(filename, header_lists, contents, titles=None, sheets=1):
    """ 多个sheets 页面 """
    myxls = XLS()
    for idx in range(sheets):
        if titles and titles[idx]:
            myxls.set_title(titles[idx], 0, len(header_lists[idx]) - 1)
        myxls.set_header(header_lists[idx])  ##写表头
        myxls.set_content(contents[idx])  ##写内容
        myxls.add_sheet('sheet{0}'.format(idx))
    path = os.path.join('download/', filename)
    myxls.save(path)
    return filename.strip('.xls')


if __name__ == '__main__':
    # xls = XLS()
    # headers = ['列1', '列2', '列3']
    # xls.set_title('测试',  0 , len(headers)-1)
    # xls.set_header(headers)
    # xls.set_content([['1', '2', '3'], ['4', '5', '6']])
    # xls.add_sheet('sheet2')
    # xls.set_title('测试',  0 , len(headers)-1)
    # xls.set_header(headers)
    # xls.set_content([['1', '2', '3'], ['4', '5', '6']])
    # xls.save('demo1.xls')
    filename = 'demo001.xls'
    header_lists = [['列1', '列2', '列3'], ['列1', '列2', '列3']]
    contents = [[['1', '2', '3'], ['4', '5', '6']], [['1', '2', '3'], ['4', '5', '6']]]
    titles = ['test1', 'test2']
    out_to_excel_sheets(filename, header_lists, contents, titles=titles, sheets=2)
